package com.yixin;

import com.yixin.entity.SmallCompany;
import com.yixin.service.SmallCompanyService;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

public class ExcelReader {

    public static void readExcel(String filePath) {
        try (InputStream inputStream = new FileInputStream(new File(filePath));
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);

            System.out.println("正在读取: " + workbook.getClass().getSimpleName());

            int n = 0;
            // 遍历每一行
            for (Row row : sheet) {
                n++;
                if (n == 1) continue;
                // 跳过空行（比如第一行是标题但为空）
                if (row == null) continue;

//                StringBuilder sb = new StringBuilder();
                // 遍历每一列
//                for (Cell cell : row) {
//                    String value = getCellValue(cell);
//                    if ( value.contains("序号") || value.contains("SUBTOTAL") ) {
//                        continue;
//                    }
//                    sb.append(value).append("\t");
//                }
//                System.out.println(sb.toString());
                Cell name = row.getCell(1); // 名称
                Cell userName = row.getCell(2); // 经营者姓名
                String phone = getCellValue(row.getCell(3)); // 手机
                // 如果phone的长度大于11，说明有两个手机号，把字符串拆分成两个手机号，拆分符为[-/]
                String phone2 = "";
                if (phone.length() > 11) {
                    String[] phones = phone.split("[-/]");
                    phone = phones[0];
                    phone2 = phones[1];
                }
                Cell address = row.getCell(4); // 经营者地址
                Cell identity = row.getCell(5); // 身份证号
                Cell companyId = row.getCell(6); // 统一信用代码
                Cell area = row.getCell(7); // 归属区域
                Cell taxPwd = row.getCell(8); // 税务局密码
                Cell seal = row.getCell(9); // 刻章
                Cell tax = row.getCell(10); // 税务登记
                Cell ticketType = row.getCell(11); // 票种认定
                String expireDate = getCellValue(row.getCell(13));// 到期日
                Cell workType = row.getCell(15); // 工种
                String remark = getCellValue(row.getCell(16)) + getCellValue(row.getCell(25));
                String proxyDate = getCellValue(row.getCell(17));
                List list = DateRangeParser.parseMonthRange(proxyDate);
                if (!list.isEmpty()) {
                    System.out.println("代理时间：");
                    LocalDate proxyDateStart = (LocalDate) list.get(0);
                    LocalDate proxyDateEnd = (LocalDate) list.get(1);
                    System.out.println(proxyDateStart);
                    System.out.println(proxyDateEnd);
                }
                Cell documentDesc = row.getCell(20); // 材料走向
                Cell contactPerson = row.getCell(22); // 对接人
                String bankInfo = getCellValue(row.getCell(24)) + getCellValue(row.getCell(23));
                Cell hasLicense = row.getCell(26); // 是否有营业执照
                Cell licenseLocation = row.getCell(27); // 营业执照位置
                Cell hasSeal = row.getCell(28); // 是否有刻章
                Cell sealLocation = row.getCell(29); // 刻章位置
                String quotaInfo = ""; // 定额情况


                System.out.println(name);
                System.out.println(userName);
                System.out.println(phone);
                System.out.println(phone2);
                System.out.println(address);
                System.out.println(identity);
                System.out.println(companyId);
                System.out.println(area);
                System.out.println(taxPwd);
                System.out.println(seal);
                System.out.println(tax);
                System.out.println(ticketType);
                System.out.println(expireDate);
                System.out.println("代理时间：");
                System.out.println(workType);
                System.out.println(remark);
                System.out.println(proxyDate);
                System.out.println(documentDesc);
                System.out.println(contactPerson);
                System.out.println(bankInfo);
                System.out.println(hasLicense);
                System.out.println(licenseLocation);
                System.out.println(hasSeal);
                System.out.println(sealLocation);
                System.out.println(quotaInfo);

                System.out.println("----------------------------------------------");




                if (n > 1) {
                    return;
                }

            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 统一处理单元格值
    private static String getCellValue(Cell cell) {
        if (cell == null) return "";

        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> {
                if (DateUtil.isCellDateFormatted(cell)) {
                    yield cell.getDateCellValue().toString();
                } else {
                    yield String.valueOf(cell.getNumericCellValue());
                }
            }
            case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
            case FORMULA -> cell.getCellFormula(); // 或获取计算结果
            case BLANK -> "";
            default -> "未知类型";
        };
    }

    public static void main(String[] args) {
        readExcel("E:/document/新设立汇总表.xls");   // 支持 .xls
        // readExcel("example.xlsx"); // 支持 .xlsx
    }
}